from server.db.session import with_session
from typing import Dict, List
import uuid
from server.db.models.message_model import MessageModel, CustomMessageModel, MarsZhiLiaoMessageModel, \
    MarsChatWithOtherPetMessageModel, MarsTrainModeKBChatMessageModel, MarsChatWithOwnPetMessageModel, \
    MarsChatWithHuoHuoMessageModel, MarsChatWithXingXingMessageModel, MarsChatWithLeRongRongMessageModel, \
    MarsChatWithXiYangYangMessageModel, MarsChatWithPandoMessageModel, MarsChatWithPandyMessageModel, \
    MarsChatWithGuiJiHuoHuoMessageModel, MarsChatWithGuiJiXingXingMessageModel, MarsChatWithMuDanHelenMessageModel
from sqlalchemy import and_
from configs import logger
from sqlalchemy.exc import SQLAlchemyError


@with_session
def add_message_to_db(session, conversation_id: str, chat_type, query, response="", message_id=None,
                      metadata: Dict = {}):
    """
    新增聊天记录
    """
    if not message_id:
        message_id = uuid.uuid4().hex
    m = MessageModel(id=message_id, chat_type=chat_type, query=query, response=response,
                     conversation_id=conversation_id,
                     meta_data=metadata)
    session.add(m)
    session.commit()
    return m.id


@with_session
def add_message_to_mars_chat_with_own_pet_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                             instruction="-1", knowledge_base_name=""):
    """
    新增自定义聊天记录
    """
    try:
        message = MarsChatWithOwnPetMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                     query=query, response=response, instruction=instruction,
                                     knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to custom db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to custom db: {str(e)}")


@with_session
def add_message_to_mars_chat_with_xingxing_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与星星的聊天记录添加到星星的表里
    """
    try:
        message = MarsChatWithXingXingMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_xingxing db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_xingxing db: {str(e)}")


@with_session
def add_message_to_mars_chat_with_huohuo_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                                  instruction="-1", knowledge_base_name=""):
    """
    将用户与火火的聊天记录添加到火火的表里
    """
    try:
        message = MarsChatWithHuoHuoMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_huohuo db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_huohuo db: {str(e)}")



@with_session
def add_message_to_mars_chat_with_other_pet_db(session, user_id: str, pet_id: str, conversation_id: str, chat_type: str,
                                               query: str, response="", instruction="-1", knowledge_base_name=""):
    """
    新增火星智聊聊天记录
    """
    try:
        message = MarsChatWithOtherPetMessageModel(user_id=user_id, pet_id=pet_id, conversation_id=conversation_id,
                                                chat_type=chat_type, query=query, response=response,
                                                instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_other_pet db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_other_pet db: {str(e)}")


@with_session
def add_message_to_mars_training_mode_kb_chat_db(session, user_id: str, conversation_id: str, chat_type: str, user_input: str,
                                                 pet_input: str = "",
                                                instruction="-1", knowledge_base_name=""):
    """
    新增火星智聊专业知识库聊天记录
    """
    try:
        message = MarsTrainModeKBChatMessageModel(user_id=user_id, conversation_id=conversation_id,
                                                     chat_type=chat_type,
                                                     pet_query=pet_input,
                                                     query=user_input, instruction=instruction,
                                                     knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_professional_kb_chat db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_professional_kb_chat db: {str(e)}")


@with_session
def update_message(session, message_id, response: str = None, metadata: Dict = None):
    """
    更新已有的聊天记录
    """
    m = get_message_by_id(message_id)
    if m is not None:
        if response is not None:
            m.response = response
        if isinstance(metadata, dict):
            m.meta_data = metadata
        session.add(m)
        session.commit()
        return m.id


@with_session
def get_message_by_id(session, message_id) -> MessageModel:
    """
    查询聊天记录
    """
    m = session.query(MessageModel).filter_by(id=message_id).first()
    return m


@with_session
def get_message_by_user_id(session, user_id, history_len=None):
    """
    通过user_id查询聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(CustomMessageModel.query, CustomMessageModel.response)
            .filter(
                and_(
                    CustomMessageModel.user_id == user_id,
                    CustomMessageModel.response != '',
                    CustomMessageModel.instruction == "-1"
                )
            )
            .order_by(CustomMessageModel.create_time.desc())
        )

        if history_len:
            messages = messages.limit(history_len)

        messages = messages.all()

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        return []

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        return []

@with_session
def get_message_by_user_id_chat_with_own_pet(session, user_id, history_len=None):
    """
    通过user_id查询聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithOwnPetMessageModel.query, MarsChatWithOwnPetMessageModel.response)
            .filter(
                and_(
                    MarsChatWithOwnPetMessageModel.user_id == user_id,
                    MarsChatWithOwnPetMessageModel.response != '',
                    MarsChatWithOwnPetMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithOwnPetMessageModel.create_time.desc())
        )

        if history_len:
            messages = messages.limit(history_len)

        messages = messages.all()

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

@with_session
def get_message_by_user_id_with_xingxing_chat(session, user_id, history_len):
    """
    通过user_id查询与星星的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithXingXingMessageModel.query, MarsChatWithXingXingMessageModel.response)
            .filter(
                and_(
                    MarsChatWithXingXingMessageModel.user_id == user_id,
                    MarsChatWithXingXingMessageModel.response != '',
                    MarsChatWithXingXingMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithXingXingMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def get_message_by_user_id_with_huohuo_chat(session, user_id, history_len):
    """
    通过user_id查询与火火的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithHuoHuoMessageModel.query, MarsChatWithHuoHuoMessageModel.response)
            .filter(
                and_(
                    MarsChatWithHuoHuoMessageModel.user_id == user_id,
                    MarsChatWithHuoHuoMessageModel.response != '',
                    MarsChatWithHuoHuoMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithHuoHuoMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

@with_session
def get_message_by_user_id_mars_zhiliao(session, user_id, history_len):
    """
    通过user_id查询火星智聊聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsZhiLiaoMessageModel.query, MarsZhiLiaoMessageModel.response)
            .filter(
                and_(
                    MarsZhiLiaoMessageModel.user_id == user_id,
                    MarsZhiLiaoMessageModel.response != '',
                    MarsZhiLiaoMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsZhiLiaoMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def get_message_by_user_id_and_pet_id(session, user_id, pet_id, history_len=None):
    """
    通过user_id和pet_id查询聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithOtherPetMessageModel.query, MarsChatWithOtherPetMessageModel.response)
            .filter(
                and_(
                    MarsChatWithOtherPetMessageModel.user_id == user_id,
                    MarsChatWithOtherPetMessageModel.pet_id == pet_id,
                    MarsChatWithOtherPetMessageModel.response != '',
                    MarsChatWithOtherPetMessageModel.instruction == "-1"
                )
            ).order_by(MarsChatWithOtherPetMessageModel.create_time.desc())
        )

        if history_len:
            messages = messages.limit(history_len)

        messages = messages.all()

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def get_message_by_user_id_professional_kb(session, user_id, history_len):
    """
    通过user_id查询用户专业模式聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsTrainModeKBChatMessageModel.query, MarsTrainModeKBChatMessageModel.response)
            .filter(
                and_(
                    MarsTrainModeKBChatMessageModel.user_id == user_id,
                    MarsTrainModeKBChatMessageModel.response != '',
                    MarsTrainModeKBChatMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsTrainModeKBChatMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def feedback_message_to_db(session, message_id, feedback_score, feedback_reason):
    """
    反馈聊天记录
    """
    m = session.query(MessageModel).filter_by(id=message_id).first()
    if m:
        m.feedback_score = feedback_score
        m.feedback_reason = feedback_reason
    session.commit()
    return m.id


@with_session
def filter_message(session, conversation_id: str, limit: int = 10):
    messages = (session.query(MessageModel).filter_by(conversation_id=conversation_id).
                # 用户最新的query 也会插入到db，忽略这个message record
                filter(MessageModel.response != '').
                # 返回最近的limit 条记录
                order_by(MessageModel.create_time.desc()).limit(limit).all())
    # 直接返回 List[MessageModel] 报错
    data = []
    for m in messages:
        data.append({"query": m.query, "response": m.response})
    return data


@with_session
def get_message_by_user_id_with_xiyangyang_chat(session, user_id, history_len):
    """
    通过user_id查询与喜洋洋的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithXiYangYangMessageModel.query, MarsChatWithXiYangYangMessageModel.response)
            .filter(
                and_(
                    MarsChatWithXiYangYangMessageModel.user_id == user_id,
                    MarsChatWithXiYangYangMessageModel.response != '',
                    MarsChatWithXiYangYangMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithXiYangYangMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e
    

@with_session
def get_message_by_user_id_with_lerongrong_chat(session, user_id, history_len):
    """
    通过user_id查询与乐融融的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithLeRongRongMessageModel.query, MarsChatWithLeRongRongMessageModel.response)
            .filter(
                and_(
                    MarsChatWithLeRongRongMessageModel.user_id == user_id,
                    MarsChatWithLeRongRongMessageModel.response != '',
                    MarsChatWithLeRongRongMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithLeRongRongMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def add_message_to_mars_chat_with_xiyangyang_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与喜洋洋的聊天记录添加到喜洋洋的表里
    """
    try:
        message = MarsChatWithXiYangYangMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_xiyangyang db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_xiyangyang db: {str(e)}")


@with_session
def add_message_to_mars_chat_with_lerongrong_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与乐融融的聊天记录添加到乐融融的表里
    """
    try:
        message = MarsChatWithLeRongRongMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_lerongrong db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_lerongrong db: {str(e)}")






















@with_session
def get_message_by_user_id_with_pando_chat(session, user_id, history_len):
    """
    通过user_id查询与pando的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithPandoMessageModel.query, MarsChatWithPandoMessageModel.response)
            .filter(
                and_(
                    MarsChatWithPandoMessageModel.user_id == user_id,
                    MarsChatWithPandoMessageModel.response != '',
                    MarsChatWithPandoMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithPandoMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e
    

@with_session
def get_message_by_user_id_with_pandy_chat(session, user_id, history_len):
    """
    通过user_id查询与pandy的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithPandyMessageModel.query, MarsChatWithPandyMessageModel.response)
            .filter(
                and_(
                    MarsChatWithPandyMessageModel.user_id == user_id,
                    MarsChatWithPandyMessageModel.response != '',
                    MarsChatWithPandyMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithPandyMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []

        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        # 捕获数据库异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        # 捕获任何异常，记录日志并返回空列表以确保稳定性
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def get_message_by_user_id_with_mudan_helen_chat(session, user_id, history_len):
    """
    通过user_id查询与牡丹Helen的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithMuDanHelenMessageModel.query, MarsChatWithMuDanHelenMessageModel.response)
            .filter(
                and_(
                    MarsChatWithMuDanHelenMessageModel.user_id == user_id,
                    MarsChatWithMuDanHelenMessageModel.response != '',
                    MarsChatWithMuDanHelenMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithMuDanHelenMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []
        
        # 反转列表，使得最新的消息在最前面
        messages.reverse()
        
        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])
        
        return result

    except SQLAlchemyError as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e


@with_session
def add_message_to_mars_chat_with_pando_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与pando的聊天记录添加到pando的表里
    """
    try:
        message = MarsChatWithPandoMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_xiyangyang db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_xiyangyang db: {str(e)}")


@with_session
def add_message_to_mars_chat_with_pandy_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与pandy的聊天记录添加到pandy的表里
    """
    try:
        message = MarsChatWithPandyMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_lerongrong db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_lerongrong db: {str(e)}")


@with_session
def add_message_to_mars_chat_with_guiji_huohuo_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与柜机火火的聊天记录添加到柜机火火的表里
    """
    try:
        message = MarsChatWithGuiJiHuoHuoMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_guiji_huohuo db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_guiji_huohuo db: {str(e)}")



@with_session
def add_message_to_mars_chat_with_guiji_xingxing_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与柜机星星的聊天记录添加到柜机星星的表里
    """     
    try:
        message = MarsChatWithGuiJiXingXingMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_guiji_xingxing db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_guiji_xingxing db: {str(e)}")

@with_session
def add_message_to_mars_chat_with_mudan_helen_db(session, user_id: str, conversation_id: str, chat_type: str, query: str, response="",
                                               instruction="-1", knowledge_base_name=""):
    """
    将用户与牡丹Helen的聊天记录添加到牡丹Helen的表里
    """
    try:
        message = MarsChatWithMuDanHelenMessageModel(user_id=user_id, conversation_id=conversation_id, chat_type=chat_type,
                                                query=query, response=response, instruction=instruction,
                                                knowledge_base_name=knowledge_base_name)
        session.add(message)
        session.commit()
    except SQLAlchemyError as e:
        logger.error(f"Error adding message to mars_chat_with_mudan_helen db: {str(e)}")
    except Exception as e:
        logger.error(f"Error adding message to mars_chat_with_mudan_helen db: {str(e)}")


@with_session
def get_message_by_user_id_with_guiji_huohuo_chat(session, user_id, history_len):
    """
    通过user_id查询与柜机火火的聊天记录，返回满足条件的格式化记录列表
    """
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithGuiJiHuoHuoMessageModel.query, MarsChatWithGuiJiHuoHuoMessageModel.response)
            .filter(
                and_(
                    MarsChatWithGuiJiHuoHuoMessageModel.user_id == user_id,
                    MarsChatWithGuiJiHuoHuoMessageModel.response != '',
                    MarsChatWithGuiJiHuoHuoMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithGuiJiHuoHuoMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []   
        
        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = [] 
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result   
    except SQLAlchemyError as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e
    

@with_session
def get_message_by_user_id_with_guiji_xingxing_chat(session, user_id, history_len):
    """
    通过user_id查询与柜机星星的聊天记录，返回满足条件的格式化记录列表
    """ 
    try:
        # 优化查询条件，直接在数据库层筛选
        messages = (
            session.query(MarsChatWithGuiJiXingXingMessageModel.query, MarsChatWithGuiJiXingXingMessageModel.response)
            .filter(
                and_(
                    MarsChatWithGuiJiXingXingMessageModel.user_id == user_id,
                    MarsChatWithGuiJiXingXingMessageModel.response != '',
                    MarsChatWithGuiJiXingXingMessageModel.instruction == "-1"
                )
            )
            .order_by(MarsChatWithGuiJiXingXingMessageModel.create_time.desc())
            .limit(history_len)
            .all()
        )

        # 如果没有符合条件的消息，直接返回空列表
        if not messages:
            return []
        
        # 反转列表，使得最新的消息在最前面
        messages.reverse()

        # 构造最终返回的列表，避免使用 np.concatenate
        result = []
        for message in messages:
            result.extend([
                {"role": "user", "content": message.query},
                {"role": "assistant", "content": message.response}
            ])

        return result
    except SQLAlchemyError as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e

    except Exception as e:
        logger.error(f"Error retrieving messages for user_id {user_id}: {str(e)}")
        raise e
        
    

